﻿// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.IO;
using System.Collections;
using System.Data.Common;

namespace net.windward.utils.ado.Excel
{
	/// <summary>
	/// This is similiar to DbProviderFactory and provides additional functionality for each provider. All properties to list
	/// if a provider can perform any task in it's child classes is provider here, not in the child classes. This
	/// class can return IWrServer objects for a given server/instance that exists on a computer. The ODBC/OleDb
	/// concept of a Provider (which is different from a DbProviderFactory) is handled as a property set in the
	/// WrProviderFactory class so all other cases do not need to implement a class for that layer.
	/// This is called WrVendor instead of WrProvider because it is different from DbProviderFactory and because
	/// we use the term Provider for the ODBC/OleDb providers.
	/// This is the class for the Excel document using it's OleDb driver.
	/// </summary>
	public class WrExcelVendor : WrVendor
	{
		/// <summary>
		/// The DbProviderFactory class for this vendor
		/// </summary>
		public const string FactoryClass = "System.Data.OleDb";

		/// <summary>
		/// The suggested name for this vendor.
		/// </summary>
		public const string FactoryName = "Excel (OleDb)";

        private string odbcProvider;

		/// <summary>
		/// Creates an Excel vendor object.
		/// </summary>
		public WrExcelVendor()
			: base(FactoryName, "The standard Excel connector", FactoryClass)
		{
		}

		/// <summary>
		/// Creates an Excel vendor object.
		/// </summary>
		/// <param name="name">The friendly name of the provider.</param>
		/// <param name="description">The description of this provider (optional).</param>
		/// <param name="providerClass">The class of the provider.</param>
		public WrExcelVendor(string name, string description, string providerClass)
			: base(name, description, providerClass)
		{
		}

		#region Properties

		/// <summary>
		/// True if can enumerate running servers from this vendor. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateServers
		{
			get { return false; }
		}

		/// <summary>
		/// True if can enumerate databases on a server. This is implemented seperately from
		/// CanCreateDataSourceEnumerator because DB2 and SqlServer implement CreateDataSourceEnumerator differently.
		/// </summary>
		public override bool CanEnumerateDatabases
		{
			get { return false; }
		}

		/// <summary>
		/// True if this vendor has the concept of databases in an installed copy of their product. This is false for Oracle (which
		/// does not have the concept of a database) and for databases like Access that are just a file - and the file is the "server."
		/// </summary>
		public override DATABASE_MODE DatabaseMode
		{
			get { return DATABASE_MODE.NEVER; }
		}

		/// <summary>
		/// True if a database supports stored procedures.
		/// </summary>
		public override bool HasStoredProcedures
		{
			get { return false; }
		}

		/// <summary>
		/// True if can launch the administrator for this vendor. This will return true if it can find the admin
		/// program file. In that case the launch could still fail.
		/// </summary>
		public override bool CanLaunchAdministrator
		{
			get { return false; }
		}

		/// <summary>
		/// True if can run DDL scripts.
		/// </summary>
		public override bool CanRunScripts
		{
			get { return false; }
		}

		/// <summary>
		/// True if can use the WindowsIdentity of the caller eliminating the need to pass a username and password.
		/// </summary>
		public override bool CanUseTrustedConnection
		{
			get { return true; }
		}

		/// <summary>
		/// True if you need to select a provider for this vendor.
		/// </summary>
		public override bool HasProviders
		{
			get { return true; }
		}

		/// <summary>
		/// Returns true if this connector is installed on the system. To be true the connector must be in the GAC and must
		/// be listed in machine.config.
		/// </summary>
		public override bool IsInstalled
		{
			get
			{
				try
				{
					// will throw exception if there is a problem
					DbProviderFactories.GetFactory(FactoryClass);
					OdbcDriverInfo[] odi = _GetOdbcProviders();
					return odi.Length > 0;
				}
				catch (Exception)
				{
					return false;
				}
			}
		}

		/// <summary>
		/// The provider factory subtype.
		/// </summary>
		public override string ProviderSubtype
		{
			get { return "Excel"; }
		}

        /// <summary>
        /// The name of the (ODBC) providers for this (vendor) provider.
        /// </summary>
        public override string OdbcProvider
        {
            get { return odbcProvider; }
            set
            {
                odbcProvider = value;
                syntax = new WrExcelSyntax(providerFactory, odbcProvider);
            }
        }

		#endregion

		#region WrProvider methods

        /// <summary>
        /// A connection string for a database on a server.
        /// </summary>
		/// <param name="filename">The Excel file to access.</param>
		/// <param name="provider">The ODBC provider used to access the file..</param>
        /// <param name="credentials">The user credentials to access the database.</param>
        /// <param name="showPassword">True to include the password, false to add the password as *****.</param>
        /// <returns>The connection string for this database on this server.</returns>
        public override string ConnectionString(string filename, string provider, WrCredentials credentials, bool showPassword)
        {
			return ConnectionString(providerFactory, provider, filename, credentials, showPassword);
        }

        internal static string ConnectionString(DbProviderFactory providerFactory,  string odbcProvider, string filename, WrCredentials credentials, bool showPassword)
		{
			if (string.IsNullOrEmpty(filename))
				return "";

            int fileExtPos = filename.LastIndexOf(".");
            string fileExtension;
            if (fileExtPos == -1)
            {
                fileExtension = "csv";
                if (!filename.Trim().EndsWith("\\"))
                    filename += "\\";
            }
            else
                fileExtension = filename.Substring(fileExtPos + 1);

			DbConnectionStringBuilder connStrBuilder = providerFactory.CreateConnectionStringBuilder();

            // Used back when the provider was apssed through (now passes the file as the odbcProvider)
            /*switch (odbcProvider.ToLower())
            {
                case "microsoft.jet.oledb.4.0 (*.xls, *.csv)":
                    connStrBuilder.Add("Provider", "Microsoft.Jet.OLEDB.4.0");
                    break;
                case "microsoft.ace.oledb.12.0 (*.xlsx, *.xlsb, *.xlsm)":
                    connStrBuilder.Add("Provider", "Microsoft.ACE.OLEDB.12.0");
                    break;
                default:
                    return "";
            }*/

            switch (fileExtension)
            {
                case "xls":
                    connStrBuilder.Add("Provider", "Microsoft.Jet.OLEDB.4.0");
                    connStrBuilder.Add("Extended Properties", "Excel 8.0;HDR=Yes;IMEX=1");
                    break;
                case "csv":
                case "txt":
                    connStrBuilder.Add("Provider", "Microsoft.Jet.OLEDB.4.0");
                    connStrBuilder.Add("Extended Properties", "text;HDR=Yes;FMT=Delimited");
                    break;
                case "xlsx":
                    connStrBuilder.Add("Provider", "Microsoft.ACE.OLEDB.12.0");
                    connStrBuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES");
                    break;
                case "xlsb":
                    connStrBuilder.Add("Provider", "Microsoft.ACE.OLEDB.12.0");
                    connStrBuilder.Add("Extended Properties", "Excel 12.0;HDR=YES");
                    break;
                case "xlsm":
                    connStrBuilder.Add("Provider", "Microsoft.ACE.OLEDB.12.0");
                    connStrBuilder.Add("Extended Properties", "Excel 12.0 Macro;HDR=YES");
                    break;
                default:
                    return "";
            }

			if (!string.IsNullOrEmpty(filename))
                if ((fileExtension != "csv") && (fileExtension != "txt"))
                    connStrBuilder.Add("Data Source", filename);
                else
                    connStrBuilder.Add("Data Source", Path.GetDirectoryName(filename));

			return connStrBuilder.ConnectionString;
		}

		/// <summary>
		/// Returns the parameters from a connection string making the best guess. Will return null for items it could not determine.
		/// </summary>
		/// <param name="connectionString">The connection string to parse.</param>
		/// <returns>The parameters.</returns>
		public override WrConnectionParams ConnectionParams(string connectionString)
		{

			if (string.IsNullOrEmpty(connectionString))
				return new WrConnectionParams();

			DbConnectionStringBuilder builder = providerFactory.CreateConnectionStringBuilder();
			builder.ConnectionString = connectionString;
			string filename = null, username = null, password = null;
			if (builder.ContainsKey("Data Source"))
				filename = (string) builder["Data Source"];
			return new WrConnectionParams(filename, null, username, password);
		}

        /// <summary>
        /// Return all (ODBC/OleDb) providers for this (vendor) provider.
        /// </summary>
        /// <exception cref="WrDbException">Thrown if this (vendor) providers does not support (ODBC/OleDb) providers.</exception>
        /// <returns>The name of each provider.</returns>
        public override OdbcDriverInfo[] GetOdbcProviders()
        {
        	return _GetOdbcProviders();
        }

        // Note: this assumes that the drivers are 32-bit and that the location of the drivers are static as of 2009-07-23.
        // This will need to be updated for 64-bit drivers(if/when) they are released and if the driver locations are
        // otherwise changes.  Does not use data factory provider to enumerate the providers since 32-bit drivers don't
        // appear to the dpf of a 64-bit application.
		private static OdbcDriverInfo[] _GetOdbcProviders()
		{
            string[] drivers = null;
			ArrayList rtn = new ArrayList();

            bool aceInstalled, jetInstalled;
            String officeVersion = OfficeVersion.getOfficeVersion().ToString();
            if (Is64BitOS())
            {
                aceInstalled = File.Exists(Environment.GetEnvironmentVariable("ProgramFiles(x86)") + "\\Common Files\\Microsoft Shared\\OFFICE" + officeVersion + "\\ACECORE.dll");
                String jetLocation = Environment.GetFolderPath(Environment.SpecialFolder.System) + "\\msjet40.dll";
                jetLocation = jetLocation.Replace("system32", "SysWOW64");
                jetInstalled = File.Exists(jetLocation);
            }
            else
            {
                aceInstalled = File.Exists(Environment.GetEnvironmentVariable("ProgramFiles") + "\\Common Files\\Microsoft Shared\\OFFICE" + officeVersion + "\\ACECORE.dll");
                String jetLocation = Environment.GetFolderPath(Environment.SpecialFolder.System) + "\\msjet40.dll";
                jetInstalled = File.Exists(jetLocation);
            }

            if(aceInstalled && jetInstalled)
                drivers = new string[2] { "Microsoft.Jet.OLEDB.4.0 (*.xls, *.csv)", "Microsoft.ACE.OLEDB.12.0 (*.xlsx, *.xlsb, *.xlsm)" };
            else if(aceInstalled)
                drivers = new string[1] { "Microsoft.ACE.OLEDB.12.0 (*.xlsx, *.xlsb, *.xlsm)" };
            else if(jetInstalled)
                drivers = new string[1] { "Microsoft.Jet.OLEDB.4.0 (*.xls, *.csv)" };
            
			foreach (string drvr in drivers)
				rtn.Add(new OdbcDriverInfo(drvr, drvr));
			OdbcDriverInfo[] odi = (OdbcDriverInfo[])rtn.ToArray(typeof(OdbcDriverInfo));
			Array.Sort(odi);
			return odi;
		}

        private static bool Is64BitOS()
        {
            if (8 == IntPtr.Size || (!String.IsNullOrEmpty(Environment.GetEnvironmentVariable("PROCESSOR_ARCHITEW6432"))))
                return true;
            return false;
        }

		#endregion

		#region DbProvider methods

		/// <summary>
		/// Returns a new instance of the provider's class that implements the WrCommand class.
		/// </summary>
		/// <returns>A new instance of WrCommand.</returns>
		public override WrCommand CreateCommand()
		{
            return new WrExcelCommand(providerFactory, odbcProvider);
		}

		/// <summary>
		/// Create a server object.
		/// </summary>
		/// <param name="server">The name of the server the database is on.</param>
		public override IWrServer CreateServer(string server)
		{
            return new WrExcelServer(providerFactory, odbcProvider, server);
		}

		#endregion

	}
}
